package com.mengjian.myspringai.mcpserver;

import com.alibaba.excel.EasyExcel;
import com.alibaba.fastjson2.JSON;
import com.mengjian.myspringai.helper.OssUploadHelper;
import com.mengjian.myspringai.model.FileUploadModel;
import com.mengjian.myspringai.model.OrderData;
import com.mengjian.myspringai.model.ShipData;
import jakarta.annotation.Resource;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.io.IOUtils;
import org.springframework.core.io.ClassPathResource;
import org.springframework.ai.tool.annotation.Tool;
import org.springframework.ai.tool.annotation.ToolParam;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.StandardCharsets;
import java.util.*;

/**
 * @author xieren
 * created by 2025/7/4
 */
@Service
@Slf4j
public class OrderMcp{
    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private OssUploadHelper ossUploadHelper;

    private final Map<String, String> sqlCache = new HashMap<>();

    private final static String ORDER_DATA_SQL = "sql/mcp_order_data.sql";
    private final static String ORDER_SHIP_DATA_SQL = "sql/mcp_order_ship_data.sql";
    private final static String ORDER_CUSTOMER_DATA_SQL = "sql/mcp_order_customer_data.sql";
    private final static String ORDER_ORDER_CUSTOMER_ALL_MANAGER_DATA_SQL = "sql/mcp_order_customer_all_manager_data.sql";


    @Tool(description = "提取订单数据，返回文件url")
    public  String extractOrderData(@ToolParam(description = "开始日期（%Y%m%d，例如20250101）") String startDate,
                                    @ToolParam(description = "结束日期（%Y%m%d，例如20250101）") String endDate) {
        log.info("获取订单数据,startDate:" + startDate + ",endDate:" + endDate);
        String sql = this.getSqlFromCache(ORDER_DATA_SQL);
        String finalSql = sql.replace("{startDate}", startDate).replace("{endDate}", endDate);
        List<OrderData> orderData = jdbcTemplate.query(finalSql,  new BeanPropertyRowMapper<>(OrderData.class));
        System.out.println(orderData);
        String fileName = "订单数据" + startDate + "-" + endDate + ".xlsx";
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        EasyExcel.write(out, OrderData.class).autoCloseStream(false).sheet(fileName).doWrite(orderData);
        FileUploadModel fileUploadModel = ossUploadHelper.ossUploadPrivate(out.toByteArray(), fileName, "test");
        return "https:" + fileUploadModel.getUrl();
    }

    @Tool(description = "获取订单发货台账数据，返回文件url")
    public String getOrderDeliveryData(@ToolParam(description = "开始日期（%Y%m%d，例如20250101）") String startDate,
                                       @ToolParam(description = "结束日期（%Y%m%d，例如20250101）") String endDate,
                                       @ToolParam(description = "供应商名称", required = false) String vendorName,
                                       @ToolParam(description = "用户token", required = true) String userToken) {

        log.info("获取订单发货台账数据,startDate:" + startDate + ",endDate:" + endDate + ",vendorName:" + vendorName);
        log.info("用户token:" + userToken);
        String sql = this.getSqlFromCache(ORDER_SHIP_DATA_SQL);
        String finalSql = sql.replace("{startDate}", startDate).replace("{endDate}", endDate);
        if (vendorName != null && !vendorName.isEmpty()) {
            finalSql = finalSql + " AND vendor_name like '%" + vendorName + "%'";
        }
        log.info("准备执行sql");
        List<ShipData> shipData = jdbcTemplate.query(finalSql, new BeanPropertyRowMapper<>(ShipData.class));
        log.info("sql执行完毕,共查询到"+ shipData.size() + "条数据");
        shipData.forEach(data -> {

            if (data.getShipQuantity() == null || data.getShipQuantity().compareTo(BigDecimal.ZERO) == 0) {
                data.setRatio(null);
            }
            String ratio = data.getRatio();
            if (ratio != null && !ratio.isEmpty()) {
                BigDecimal ratioDecimal = new BigDecimal(ratio).multiply(new BigDecimal(100)).setScale(4, RoundingMode.HALF_UP);
                data.setRatio(ratioDecimal + "%");
            }
        });

        String fileName = "订单发货台账" + startDate + "-" + endDate + ".xlsx";
        ByteArrayOutputStream out = new ByteArrayOutputStream();
        EasyExcel.write(out, ShipData.class).autoCloseStream(false).sheet(fileName)
                .doWrite(shipData);
        FileUploadModel fileUploadModel = ossUploadHelper.ossUploadPrivate(out.toByteArray(), fileName, "test");
        return "https:" + fileUploadModel.getUrl();
    }

    @Tool(description = "获取某年某业务经理新老客户的订单销售额, 返回新老客户的销售总额和客户数")
    public String getOrderCustomerData(@ToolParam(description = "业务经理姓名（必填）") String serviceManager,
                                       @ToolParam(description = "年份（必填）") Integer year) {
        System.out.println("获取某年某业务经理老客户订单销售额,serviceManager:" + serviceManager + ",year:" + year);
        String sql = this.getSqlFromCache(ORDER_CUSTOMER_DATA_SQL);
        String finalSql = sql.replace("{service_manager}", serviceManager).replace("{arg_year}", year.toString());
        System.out.println("准备执行sql");
        List<customerData> result = new ArrayList<>();
        try {
            result = jdbcTemplate.query(finalSql, new BeanPropertyRowMapper<>(customerData.class));
        } catch (Exception e) {
            e.printStackTrace();
        }

        return JSON.toJSONString(result);
    }

    @Tool(description = "获取某年所有业务经理新老客户的订单销售数据, 返回各个业务经理的新老客户的销售总额和客户数")
    public String getCustomerDataOfAllManager(@ToolParam(description = "年份（必填）") Integer year) {
        System.out.println("获取某年所有业务经理老客户订单销售额, year:" + year);
        String sql = this.getSqlFromCache(ORDER_ORDER_CUSTOMER_ALL_MANAGER_DATA_SQL);
        String finalSql = sql.replace("{arg_year}", year.toString());
        System.out.println("准备执行sql");
        List<customerData> result = new ArrayList<>();
        try {
            result = jdbcTemplate.query(finalSql, new BeanPropertyRowMapper<>(customerData.class));
        } catch (Exception e) {
            e.printStackTrace();
        }
        return JSON.toJSONString(result);
    }
    @Data
    static class customerData {
        private String serviceManager;
        private String customerType;
        private BigDecimal amount;
        private Integer customerCount;
    }

    private String getSqlFromCache(String filePath) {
        String sql = sqlCache.get(filePath);
        if (sql == null) {
            sql = loadSqlFromFile(filePath);
            sqlCache.put(filePath, sql);
        }
        return sql;
    }

    private String loadSqlFromFile(String filePath) {
        try {
            ClassPathResource resource = new ClassPathResource(filePath);
            return IOUtils.toString(resource.getInputStream(), StandardCharsets.UTF_8);
        } catch (Exception e) {
            System.out.println("Failed to read SQL file:" + e.getMessage());
            throw new RuntimeException("Failed to read SQL file: " + filePath, e);
        }
    }

}
